package com.online.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class ExcelUtil {

	private ExcelConfig config;

	public ExcelUtil(ExcelConfig config) {
		this.config = config;
	}

	public void writeExcel() throws Exception {
		// Workbook wb = WorkbookFactory.create(new
		// File(config.getSourceTemplate()));
		// Sheet sheet =
		// wb.createSheet(WorkbookUtil.createSafeSheetName("表单1"));
		XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(config.getSourceTemplate()));
		Workbook swb = new SXSSFWorkbook(wb, 500);
		Sheet sheet = swb.getSheetAt(0);
		Row title = wb.getSheetAt(0).getRow(0);
		Row templateRow = wb.getSheetAt(0).getRow(1);
		String[] template = new String[templateRow.getLastCellNum()]; // 属性集合
		CellStyle[] cellStyles = new CellStyle[templateRow.getLastCellNum()];
		Integer rowNum = wb.getSheetAt(0).getLastRowNum(); // 模板已有行
		Integer pageSize = config.getPageSize();
		Integer pageCount = 0;
		Row row;
		Cell cell;
		List<?> list = config.getData();
		Map<String, BigDecimal> sumMap = new HashMap<String, BigDecimal>(); // 汇总列
		BigDecimal sum;
		Object value;

		if (pageSize != null) {
			pageCount = (int) Math.ceil(((float) list.size()) / pageSize);
		}
		if (config.getSumColumn() != null) {
			for (String prop : config.getSumColumn()) {
				sumMap.put(prop, new BigDecimal(0));
			}
		}

		// 获取模板行
		for (int i = 0; i < templateRow.getLastCellNum(); i++) {
			template[i] = this.getCellValue(templateRow.getCell(i));
			cellStyles[i] = templateRow.getCell(i).getCellStyle();
		}

		// 写入内容
		for (int i = 0; i < list.size(); i++) {
			Object obj = list.get(i);

			// 标题
			if (pageSize != null) {
				if (i >= pageSize && i % pageSize == 0) {
					row = sheet.createRow(rowNum++);
					this.copy(title, row);
					// 假设标题第一个单元格有页码信息
					cell = row.getCell(0);
					cell.setCellValue(MessageFormat.format(cell.getStringCellValue(), i / pageSize + 1, pageCount));
				}
			}

			// 内容行
			row = sheet.createRow(rowNum++);
			for (int j = 0; j < template.length; j++) {
				cell = row.createCell(j);
				value = PropertyUtils.getProperty(obj, template[j]);
				cell.setCellStyle(cellStyles[j]);
				this.setCellValue(cell, value);

				// 汇总
				if (value != null && sumMap.containsKey(template[j])) {
					sum = sumMap.get(template[j]);
					sum = sum.add(new BigDecimal(value.toString()));
					sumMap.put(template[j], sum);
				}
			}
		}

		// 处理第一行标题的页码
		if (pageSize != null) {
			cell = title.getCell(0);
			cell.setCellValue(MessageFormat.format(cell.getStringCellValue(), 1, pageCount));
		}

		// 处理汇总
		if (config.getSumColumn() != null) {
			CellStyle style;
			//设置字体
			Font font = swb.createFont();
			font.setColor(IndexedColors.RED.getIndex());
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			row = sheet.createRow(rowNum++);
			for (int i = 0; i < template.length; i++) {
				//注意这里要从原有样式里clone，否则设置单元格样式后，会影响到整列。
				style = swb.createCellStyle();
				style.cloneStyleFrom(cellStyles[i]);
				//前景色
				style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
				style.setFillPattern(CellStyle.SOLID_FOREGROUND);
				style.setFont(font);
				cell = row.createCell(i);
				cell.setCellStyle(style);
				if (sumMap.containsKey(template[i])) {
					if (sumMap.get(template[i]).toString().indexOf(".") == -1) {
						cell.setCellValue(sumMap.get(template[i]).longValue());
					} else {
						cell.setCellValue(sumMap.get(template[i]).doubleValue());
					}
				}
			}
		}

		// 自动调整列
		if (config.getAutoSize()) {
			for (int i = 0; i < template.length; i++) {
				sheet.autoSizeColumn(i);
			}
		} else if (config.getAutoSizeColumn() != null) {
			for (int i = 0; i < template.length; i++) {
				for (String auto : config.getAutoSizeColumn()) {
					if (StringUtils.equals(auto, template[i])) {
						sheet.autoSizeColumn(i);
					}
				}
			}
		}

		writeFile(swb);
	}

	/** 写入目标文件 */
	private void writeFile(Workbook wb) throws IOException {
		FileOutputStream out = null;
		try {
			new File(config.getTargetFile()).getParentFile().mkdirs();
			out = new FileOutputStream(config.getTargetFile());
			wb.write(out);
		} catch (IOException e) {
			throw e;
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}

	/** 获取单元格值 */
	public String getCellValue(Cell cell) {
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			return new BigDecimal(cell.getNumericCellValue()).toString();
		case Cell.CELL_TYPE_BOOLEAN:
			return new Boolean(cell.getBooleanCellValue()).toString();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		}
		return cell.getStringCellValue();
	}

	/** 设置单元格值 */
	public void setCellValue(Cell cell, Object value) {
		if (value == null) {
			cell.setCellValue("");
			return;
		}
		if (value instanceof Number) {
			cell.setCellValue(new BigDecimal(value.toString()).doubleValue());
		} else if (value instanceof Date) {
			if (config.getDateFormat() == null) {
				cell.setCellValue((Date) value);
			} else {
				cell.setCellValue(config.getDateFormat().format((Date) value));
			}
		} else {
			cell.setCellValue(value.toString());
		}
	}

	/** 将source复制到target */
	public Row copy(Row source, Row target) {
		target.setHeight(source.getHeight());
		Cell cell;
		for (int i = 0; i < source.getLastCellNum(); i++) {
			cell = target.createCell(i);
			cell.setCellValue(source.getCell(i).getStringCellValue());
			cell.setCellStyle(source.getCell(i).getCellStyle());
		}
		return target;
	}
}
